import pandas as pd

# 读取 txt 文件
with open('datas/students_info.txt', 'r', encoding='utf-8') as file:
    lines = file.readlines()
    student_info = {}
    for line in lines:
        parts = line.strip().split('，')
        student_id = parts[0].split('：')[1]
        name = parts[1].split('：')[1]
        student_info[student_id] = name
# 读取 csv 文件
scores_data = pd.read_csv('datas/students_scores.csv')
average_scores = scores_data.groupby('学号')['成绩'].mean().reset_index()
# 读取 xlsx 文件
excel_file = pd.ExcelFile('datas/students_classes.xlsx')
class_data = excel_file.parse('班级信息')
# 整合数据
merged_data = pd.merge(class_data, average_scores, on='学号', how='left')
merged_data['姓名'] = merged_data['学号'].map(student_info)
# 调整列顺序并写入新的 csv 文件
result = merged_data[['学号', '姓名', '班级', '成绩']].rename(columns={'成绩': '平均成绩'})
result.to_csv('datas/integrated_student_data.csv', index=False)
